﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using System.Collections.ObjectModel;
using dtSearch32Lib;
using System.Collections;
using Portal.Cms.Model;
using Portal.Database.Service;
using Portal.Web.Utility;
using Portal.Core;

namespace Portal.Cms.Bussiness
{
    public class DocStatiticsProcessBll
    {
        public static ArrayList ReadingDocumentStatitics(
            string SiteId,
            string DateField,
            DateTime FromDate,
            DateTime ToDate)
        {
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            string Query =
                    " select Id=DocTypeId,Subject=DocTypeName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocTypeId=DocTypeId and " + DateField + " between '" + FromDate.ToString("yyyy/MM/dd 00:00") + "' and '" + ToDate.ToString("yyyy/MM/dd 23:59") + "') from TableDocType " +
                    "     where AssetCode=@AssetCode order by DocTypeName; " +
                    " select Id=DocFieldId,Subject=DocFieldName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocFieldId=DocFieldId and " + DateField + " between '" + FromDate.ToString("yyyy/MM/dd 00:00") + "' and '" + ToDate.ToString("yyyy/MM/dd 23:59") + "') from TableDocField " +
                    "     where AssetCode=@AssetCode order by DocFieldName; " +
                    " select Id=DocSignerId,Subject=DocSignerName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocSignerId=DocSignerId and " + DateField + " between '" + FromDate.ToString("yyyy/MM/dd 00:00") + "' and '" + ToDate.ToString("yyyy/MM/dd 23:59") + "') from TableDocSigner " +
                    "     where AssetCode=@AssetCode order by DocSignerName; " +
                    " select Id=DocReleaseFromId, Subject=DocReleaseFromName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocReleaseFromId=DocReleaseFromId and " + DateField + " between '" + FromDate.ToString("yyyy/MM/dd 00:00") + "' and '" + ToDate.ToString("yyyy/MM/dd 23:59") + "') from TableDocReleaseFrom " +
                    "     where AssetCode=@AssetCode order by DocReleaseFromName; " +
                    " select Id=DocYearRangeId, Subject=DocYearRangeName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocYearRangeId=DocYearRangeId and " + DateField + " between '" + FromDate.ToString("yyyy/MM/dd 00:00") + "' and '" + ToDate.ToString("yyyy/MM/dd 23:59") + "') from TableDocYearRange " +
                    "     where AssetCode=@AssetCode order by DocYearRangeName; ";


            //" select Id=DocTypeId,Subject=DocTypeName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocTypeId=DocTypeId and " + DateField + " between '" + FromDate.ToString("MM/dd/yyyy 00:00") + "' and '" + ToDate.ToString("MM/dd/yyyy 23:59") + "') from TableDocType " +
            //        "     where AssetCode=@AssetCode order by DocTypeName; " +
            //        " select Id=DocFieldId,Subject=DocFieldName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocFieldId=DocFieldId and " + DateField + " between '" + FromDate.ToString("MM/dd/yyyy 00:00") + "' and '" + ToDate.ToString("MM/dd/yyyy 23:59") + "') from TableDocField " +
            //        "     where AssetCode=@AssetCode order by DocFieldName; " +
            //        " select Id=DocSignerId,Subject=DocSignerName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocSignerId=DocSignerId and " + DateField + " between '" + FromDate.ToString("MM/dd/yyyy 00:00") + "' and '" + ToDate.ToString("MM/dd/yyyy 23:59") + "') from TableDocSigner " +
            //        "     where AssetCode=@AssetCode order by DocSignerName; " +
            //        " select Id=DocReleaseFromId, Subject=DocReleaseFromName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocReleaseFromId=DocReleaseFromId and " + DateField + " between '" + FromDate.ToString("MM/dd/yyyy 00:00") + "' and '" + ToDate.ToString("MM/dd/yyyy 23:59") + "') from TableDocReleaseFrom " +
            //        "     where AssetCode=@AssetCode order by DocReleaseFromName; " +
            //        " select Id=DocYearRangeId, Subject=DocYearRangeName,Total=(select count(DataId) from TableData join TableGroupData on GroupDataId=frkGroupDataId  where Alias='vbpq' and frkDocYearRangeId=DocYearRangeId and " + DateField + " between '" + FromDate.ToString("MM/dd/yyyy 00:00") + "' and '" + ToDate.ToString("MM/dd/yyyy 23:59") + "') from TableDocYearRange " +
            //        "     where AssetCode=@AssetCode order by DocYearRangeName; ";


            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                    });

            ArrayList DocStatitics = new ArrayList();
            for(int iIndex=0;iIndex<dsResult.Tables.Count;iIndex++)
            {
                DocStatiticsCls[] ItemDocStatitics = DocStatiticsParser.ParseFromDataTable(dsResult.Tables[iIndex]);
                DocStatitics.Add(ItemDocStatitics);
            }
            return DocStatitics;
        }



        public static CmsDataCls[] ReadingViewDownloadStatitics(
            string SiteId,
            string ViewType,
            int    MaxDoc)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            string Query =
                    " select TOP " + MaxDoc + " *,DocTypeName,DocFieldName,DocReleaseFromName,DocSignerName from TableData  " +
                    "       join TableGroupData on GroupDataId=frkGroupDataId  "+
                    "       left outer join TableDocType on TableDocType.DocTypeId=frkDocTypeId"+
                    "       left outer join TableDocField on TableDocField.DocFieldId=frkDocFieldId" +
                    "       left outer join TableDocSigner on TableDocSigner.DocSignerId=frkDocSignerId" +
                    "       left outer join TableDocReleaseFrom on TableDocReleaseFrom.DocReleaseFromId=TableData.frkDocReleaseFromId" +
                    "     where TableData.AssetCode=@AssetCode and Alias='vbpq' ";
            if (ViewType.Equals("view"))
            {
                Query +=
                    " order by TotalPreview DESC ";
            }
            else
            {
                Query +=
                    " order by TotalDownload DESC ";
            }
                   
            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                    });

            CmsDataCls[] CmsData = CmsDataParser.ParseFromDataTable(dsResult.Tables[0]);
            return CmsData;
        }


        public static CmsDataCls[] ReadingCmsData(
            string SiteId,
            string DateField,
            DateTime FromDate,
            DateTime ToDate,
            string Field,
            string Id)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            IDatabaseService DBService = WebDatabaseService.CreateDBService(SiteId);
            string AssetCode = WebSessionUtility.GetCurrentLoginUser().AssetCode;
            string DataLanguage = WebConfig.GetDataLanguage(OSiteParam);
            string Query =
                    " select * from TableData where AssetCode=@AssetCode and "+DateField+" between '"+FromDate.ToString("MM/dd/yyyy 00:00")+"' and '"+ToDate.ToString("MM/dd/yyyy 23:59")+"'";
            if (Field.Equals("DocField"))
            {
                Query += " and frkDocFieldId='" + Id + "' ";
            }
            if (Field.Equals("DocType"))
            {
                Query += " and frkDocTypeId='" + Id + "' ";
            }
            if (Field.Equals("DocSigner"))
            {
                Query += " and frkDocSignerId='" + Id + "' ";
            }
            if (Field.Equals("DocYearRange"))
            {
                Query += " and frkDocYearRangeId='" + Id + "' ";
            }
            if (Field.Equals("DocReleaseFrom"))
            {
                Query += " and frkDocReleaseFromId='" + Id + "' ";
            }
            
            DataSet dsResult =
                    DBService.GetDataSet(null, Query,
                    new DbParam[] 
                    { 
                        new DbParam("AssetCode", AssetCode),
                    });
            CmsDataCls[] CmsData = CmsDataParser.ParseFromDataTable(dsResult.Tables[0]);
            return CmsData;
        }
    }
}
